<?php
MYTCONN();

function n_fmt($value) {
    $value = number_format($value, 2, ',', '.');
    return $value;
}

// VARS
$FltDate = $_POST['TimeType'];
$QORI1 = '';
$QCorresponsal = '';
$QLabelHRow = $_POST['Ordering2'];

$TCOUNTER = 0;
$TOTAL1 = 0;
$TOTAL2 = 0;
$TOTAL3 = 0;
$TOTAL4 = 0;
$TOTAL5 = 0;
$TOTAL6 = 0;
$TOTAL7 = 0;
$TOTAL8 = 0;
$TOTAL9 = 0;
$TOTAL10 = 0;

$prtContent = '';

$GETMARK = $GLOBALS['MYT']->db_array("SELECT CntValor FROM contadores WHERE CntCampo = 10");

if ($_POST['Ordering2'] == 'DateDay') {
    $_POST['Ordering2'] = 'DATE(`' . $_POST['TimeType'] . '`)';
}
if ($_POST['OrderBy'] == 'DateDay') {
    $_POST['OrderBy'] = 'DATE(`' . $_POST['TimeType'] . '`)';
}

if (isset($_POST['Offices'])) {
    $QORI1 = " AND `FraOrigen0`  IN (".implode($_POST['Offices']).") ";
}

if (isset($_POST['Op_Corr'])) {
    $QCorresponsal = " AND `Op_Corr`  IN (".implode($_POST['Op_Corr']).") ";
}
$Country = '';
if (isset($_POST['BnfPais'])) {
    $Country = " AND (FraBnfPais IN (".implode(',',$_POST['BnfPais']).") OR BnfPais IN (".implode(',',$_POST['BnfPais'])."))";
}
// Printers

$prtContent .= '<style media="print" type="text/css">
td { font-size:12px;
	font-family: Verdana; }
</style>';


// Reports

if (isset($_POST['Ordering']) && $_POST['Ordering'] == "FraCargo") {

    $prtContent .= '<div class="Line">Consulta de Taxas</div>
		<table width="100%"  border="0">
			<tr align="center" bgcolor="#FEF75F">
			  <td NOWRAP width="25%">N&uacute;mero de Remessas </td>
			  <td NOWRAP width="25%">Taxas</td>
			  <td NOWRAP width="25%">Prom.</td>
			  <td>Comiss&otilde;es</td>
			  <td>Valor das Remessas Cliente </td>
			  <td NOWRAP width="25%">Total ComissÃo </td>
			  <td NOWRAP width="25%">Total Taxas </td>
		  </tr>';

    $QList = $GLOBALS['MYT']->db_array("SELECT
		FraMonCobro,
		FraMonDPago,
		Op_Orden AS ref,
		" . $FltDate . " AS QDate,
		Op_Corr AS Op_Corr,
		SUM(FraEnviado+FraProm+FraRetencion) AS FraEnviado,
		SUM(FraRetencion) AS FraRetencion,
		SUM(FraARecibir) AS FraARecibir,
		SUM(FraARecibir/{$_POST['RATETYPE']}) AS SELLVALUE,
		Op_Orden,
		SUM(FraCargo) AS FraCargo,
		SUM(FraProm) AS FraProm
		SUM(FraComisionAg) AS FraComisionAg,
		SUM(FraComisionCorr) AS FraFraComisionCorr,
		{$_POST['Ordering']} AS Ordering,
		SUM(IF(FraStatus IN (10,21),-1,1)) AS COUNTER
		FROM ordenespago
		INNER JOIN ordenesfinanceiro ON Of_Orden = Op_Orden
		INNER JOIN facturas ON FraOrdenPago = Op_Orden
		LEFT JOIN facturasextjoins ON FraID = FraExtID
		LEFT JOIN beneficiarios ON FraBnfNumero = BnfNumero
		INNER JOIN corresponsales ON CorrNumero = Op_Corr
		INNER JOIN origenes ON OriNumero = FraOrigen0
		WHERE " . $FltDate . " BETWEEN '{$_POST['TimeStart']} 00:00:00' AND '{$_POST['TimeEnd']} 23:59:59'
		AND {$_POST['RATETYPE']} != ''
		AND FraStatus IN ({$_POST['status']})
		{$QCorresponsal}$Country
		GROUP BY {$_POST['Ordering']},FraMonDPago,FraMonCobro
		ORDER BY {$_POST['Ordering']}");
    $CARD = $QList[0];
    for ($q = 1; $q <= $QList[0]; $q++) {
        $QList[$q]['FraMonDPago'] = '';
        $QList[$q]['FraMonCobro'] = '';

        $prtContent .= '<tr bgcolor="#F7F7F7">
			  <td>' . $QList[$q]['COUNTER'] . '</td>
			  <td NOWRAP align="right">' . $QList[$q]['FraMonDPago'] . n_fmt($QList[$q]['FraCargo'] / $QList[$q]['COUNTER']) . '</td>
			  <td NOWRAP align="right">&nbsp;</td>
			  <td NOWRAP align="right">' . n_fmt($QList[$q]['FraEnviado']) . $QList[$q]['FraMonDPago'] . '</td>
			  <td NOWRAP align="right">&nbsp;</td>
			  <td NOWRAP align="right">' . n_fmt($QList[$q]['FraCargo']) . $QList[$q]['FraMonDPago'] . '</td>
			  <td NOWRAP align="right">' . n_fmt($QList[$q]['FraProm']) . $QList[$q]['FraMonDPago'] . '</td>
		</tr>';

        $QLists = $GLOBALS['MYT']->db_array("SELECT
		FraMonCobro,
		FraMonDPago,
		Op_Orden AS ref,
		" . $FltDate . " AS QDate,
		Op_Corr AS Op_Corr,
		SUM(FraEnviado+FraProm+FraRetencion) AS FraEnviado,
		SUM(FraRetencion) AS FraRetencion,
		SUM(FraARecibir) AS FraARecibir,
		SUM(FraARecibir/{$_POST['RATETYPE']}) AS SELLVALUE,
		OriNombre AS off,
		Op_Orden,
		SUM(FraCargo) AS FraCargo,
		SUM(FraProm) AS FraProm,
		SUM(FraComisionAg) AS FraComisionAg,
		SUM(FraComisionCorr) AS FraFraComisionCorr,
		{$_POST['Ordering2']},
		SUM(IF(FraStatus IN (10,21),-1,1)) AS COUNTER
		FROM ordenespago
		INNER JOIN ordenesfinanceiro ON Of_Orden = Op_Orden
		INNER JOIN facturas ON FraOrdenPago = Op_Orden
		LEFT JOIN facturasextjoins ON FraID = FraExtID
		LEFT JOIN beneficiarios ON FraBnfNumero = BnfNumero
		INNER JOIN corresponsales ON CorrNumero = Op_Corr
		INNER JOIN origenes ON OriNumero = FraOrigen0
		WHERE " . $FltDate . " BETWEEN '{$_POST['TimeStart']} 00:00:00' AND '{$_POST['TimeEnd']} 23:59:59'
		AND {$_POST['RATETYPE']} != ''
		AND FraStatus IN ({$_POST['status']})
		AND {$_POST['Ordering']} = '{$QList[$q]['Ordering']}'
		{$QCorresponsal}{$Country}
		GROUP BY {$_POST['Ordering2']},FraMonDPago,FraMonCobro
		ORDER BY {$_POST['Ordering2']}");
        $CARB = $QLists[0];
        for ($r = 1; $r <= $CARB; $r++) {
            $QLists[$r]['FraMonDPago'] = '';
            $QLists[$r]['FraMonCobro'] = '';
            $QRowLabel = $QLists[$r][$_POST['Ordering2']];

            if ($_POST['Ordering2'] == 'FraOrigen0') {
                $QRowLabel = $QLists[$r]['off'];
            }

            $prtContent .= '<tr bgcolor="' . gridlines($r) . '">
			  <td>&nbsp;&nbsp;&nbsp;' . $QLists[$r]['COUNTER'] . '</td>
			  <td>' . $QRowLabel . '</td>
			  <td NOWRAP align="right">&nbsp;</td>
			  <td NOWRAP align="right">' . n_fmt($QLists[$r]['FraEnviado']) . $_SESSION[$SESSST . 'CURS'][$QLists[$r]['FraMonDPago']]['CurLabel'] . '</td>
			  <td NOWRAP align="right">&nbsp;</td>
			  <td NOWRAP align="right">' . n_fmt($QLists[$r]['FraCargo']) . $_SESSION[$SESSST . 'CURS'][$QLists[$r]['FraMonDPago']]['CurLabel'] . '</td>
		  </tr>';
        }
    }
    $prtContent .= '</table>';
} else {

    if ($_POST['Ordering2'] == 'Op_Orden') {
        $QLabelHRow = 'Banco';
    } elseif ($_POST['Ordering2'] == 'FraOrigen0') {
        $QLabelHRow = 'Loja';
    } elseif ($_POST['Ordering2'] == 'FraOrigen1') {
        $QLabelHRow = 'Origem';
    }

    $prtContent .= '<div class="Line">Consulta de OperaÇ&otilde;es</div>
	<table width="100%" border="0">
        <tr bgcolor="#FEF75F" style="height:20px">
          <td NOWRAP width="9%">Data</td>
          <td NOWRAP width="13%">' . $QLabelHRow . '</td>';
    if ($_POST['Ordering2'] == 'Op_Orden,OriNombre') {
        $prtContent .= '<td NOWRAP width="9%">Lote</td>';
    }
    $prtContent .= '<td NOWRAP width="3%">#</td>
          <td NOWRAP width="12%">Remessa Cliente </td>
          <td NOWRAP width="6%" bgcolor="#FEF75F">Taxas</td>
          <td NOWRAP width="6%" bgcolor="#FEF75F">Prom.</td>
          <td NOWRAP width="6%" bgcolor="#FEF75F">Retido</td>
		  <td NOWRAP width="5%">Com.Money</td>
		  <td NOWRAP width="5%">Com.Corr</td>
		  <td NOWRAP width="5%">Imp.Selo</td>
          <td NOWRAP width="5%">C&acirc;mbio BalcÃo</td>
          <td NOWRAP width="10%">Resultado</td>
          <td NOWRAP width="5%">C&acirc;mbio Negociado </td>
          <td NOWRAP width="10%">Resultado Financeiro </td>
          <td NOWRAP width="9%">Margem de Lucro</td>
          <td NOWRAP width="22%">Lucro</td>
      </tr>';
    $LabelCOL = $_POST['Ordering2'];
    # FiX para mostrar numero de remessa
    if ($LabelCOL == 'FraID') {
        $LabelCOL = "CONCAT(FraOrigen0,'-',FraReferenciaAg)";
    }
    $QList = $GLOBALS['MYT']->db_array("SELECT
	`FraMonCobro`,
	`FraMonDPago`,
	{$LabelCOL} AS `Label`,
	DATE(`" . $FltDate . "`) AS `QDate`,
	`CorrAlias`,
	SUM(`FraEnviado`+`FraProm`) AS `FraEnviado`,
	SUM(`FraRetencion`) AS `FraRetencion`,
	SUM(`FraARecibir`) AS `FraARecibir`,
	SUM(`FraARecibir`/{$_POST['RATETYPE']}) AS `SELLVALUE`,
	`Op_Orden`,
	`Op_Corr`,
	`OriNombre`,
	SUM(`FraCargo`) AS `FraCargo`,
	SUM(`FraProm`) AS `FraProm`,
	SUM(`FraComisionAg`) AS `FraComisionAg`,
	SUM(`FraComisionCorr`) AS `FraComisionCorr`,
	SUM(IF(`FraStatus` IN (10,21),-1,1)) AS `COUNTER`
	FROM `facturas` IGNORE INDEX (`OrdenPago`)
	LEFT JOIN facturasextjoins ON FraID = FraExtID
	LEFT JOIN beneficiarios ON FraBnfNumero = BnfNumero
	INNER JOIN `origenes` ON `OriNumero` = `FraOrigen0`
	INNER JOIN `ordenespago` ON `FraOrdenPago` = `Op_Orden`
	INNER JOIN `ordenesfinanceiro` ON `Of_Orden` = `Op_Orden`
	INNER JOIN `corresponsales` ON `CorrNumero` = `Op_Corr`
	WHERE `" . $FltDate . "` BETWEEN '{$_POST['TimeStart']} 00:00:00' AND '{$_POST['TimeEnd']} 23:59:59'
	AND `FraStatus` IN ({$_POST['status']})
	{$QCorresponsal}{$Country}
	{$QORI1}
	GROUP BY {$_POST['Ordering2']},FraMonDPago,FraMonCobro
	ORDER BY {$_POST['Ordering2']}");
    $CARD = $QList[0];
    for ($q = 1; $q <= $CARD; $q++) {

        if ($_POST['Ordering2'] == 'Op_Orden') {
            $QRowLabel = $QList[$q]['CorrAlias'];
        } elseif ($_POST['Ordering2'] == 'FraOrigen0' || $_POST['Ordering2'] == 'FraOrigen1') {
            $QRowLabel = $QList[$q]['OriNombre'];
        } else {
            $QRowLabel = $QList[$q]['Label'];
        }

        // Comissioes e Taxas
        $COMM1 = (($QList[$q]['FraComisionCorr'] / 3) * 1);
        $COMM2 = (($QList[$q]['FraComisionCorr'] / 3) * 2);

        $DMEAMOUNT = 1;
        $DMOAMOUNT = 1;

        if ($QList[$q]['FraEnviado'] != 0) {
            $DMEAMOUNT = $QList[$q]['FraEnviado'];
            $DMOAMOUNT = $QList[$q]['SELLVALUE'];
        }

        $BUYRATE = $QList[$q]['FraARecibir'] / $DMEAMOUNT;
        $SELLRATE = $QList[$q]['FraARecibir'] / $DMOAMOUNT;

        $ProfitAmount = $QList[$q]['FraEnviado'] - $QList[$q]['SELLVALUE'];

        // Totais
        $TCOUNTER += $QList[$q]['COUNTER'];
        $TOTAL1 += $QList[$q]['FraEnviado'];
        $TOTAL2 += $QList[$q]['FraCargo'];
        $TOTAL3 += $QList[$q]['FraRetencion'];
        $TOTAL4 += $COMM1;
        $TOTAL5 += $COMM2;
        $TOTAL6 += $QList[$q]['FraComisionAg'];
        $TOTAL7 += $QList[$q]['FraARecibir'];
        $TOTAL8 += $QList[$q]['SELLVALUE'];
        $TOTAL9 += $ProfitAmount;
        $TOTAL10 += $QList[$q]['FraProm'];
        $CURR1 = $_SESSION[$SESSST . 'CURS'][$QList[$q]['FraMonCobro']]['CurLabel'];
        $CURR2 = $_SESSION[$SESSST . 'CURS'][$QList[$q]['FraMonDPago']]['CurLabel'];

        $prtContent .= '<tr align="right" bgcolor="' . gridlines($q) . '" height="24">
          <td NOWRAP align="left">' . $QList[$q]['QDate'] . '</td>
          <td NOWRAP align="left">' . $QRowLabel . '</td>';
        if ($_POST['Ordering2'] == 'Op_Orden,OriNombre') {
            $prtContent .= '<td NOWRAP align="left">' . $QList[$q]['Op_Orden'] . '</td>';
        }
        $prtContent .= '
          <td NOWRAP align="right">' . $QList[$q]['COUNTER'] . '</td>
          <td NOWRAP align="right">' . n_fmt($QList[$q]['FraEnviado']) . $_SESSION[$SESSST . 'CURS'][$QList[$q]['FraMonCobro']]['CurLabel'] . '</td>
		  <td NOWRAP align="right">' . n_fmt($QList[$q]['FraCargo']) . $_SESSION[$SESSST . 'CURS'][$QList[$q]['FraMonCobro']]['CurLabel'] . '</td>
		  <td NOWRAP align="right">' . n_fmt($QList[$q]['FraProm']) . $_SESSION[$SESSST . 'CURS'][$QList[$q]['FraMonCobro']]['CurLabel'] . '</td>
		  <td NOWRAP align="right">' . n_fmt($QList[$q]['FraRetencion']) . $_SESSION[$SESSST . 'CURS'][$QList[$q]['FraMonCobro']]['CurLabel'] . '</td>
		  <td NOWRAP align="right">' . n_fmt($COMM1) . $_SESSION[$SESSST . 'CURS'][$QList[$q]['FraMonCobro']]['CurLabel'] . '</td>
          <td NOWRAP align="right">' . n_fmt($COMM2) . $_SESSION[$SESSST . 'CURS'][$QList[$q]['FraMonCobro']]['CurLabel'] . '</td>
          <td NOWRAP align="right">' . n_fmt($QList[$q]['FraComisionAg']) . $_SESSION[$SESSST . 'CURS'][$QList[$q]['FraMonCobro']]['CurLabel'] . '</td>
		  <td NOWRAP align="right">' . number_format($BUYRATE, 4) . '</td>
          <td NOWRAP align="right">' . n_fmt($QList[$q]['FraARecibir']) . $_SESSION[$SESSST . 'CURS'][$QList[$q]['FraMonDPago']]['CurLabel'] . '</td>
          <td NOWRAP align="right">' . number_format($SELLRATE, 4) . '</td>
          <td NOWRAP align="right">' . n_fmt($QList[$q]['SELLVALUE']) . $_SESSION[$SESSST . 'CURS'][$QList[$q]['FraMonCobro']]['CurLabel'] . '</td>
          <td NOWRAP align="right">' . number_format($ProfitAmount * 100 / $DMEAMOUNT, 4) . '%</td>
          <td NOWRAP align="right">' . n_fmt($ProfitAmount) . $_SESSION[$SESSST . 'CURS'][$QList[$q]['FraMonCobro']]['CurLabel'] . '</td></tr>';
    }
    $prtContent .= '<tr><td colspan="20"><hr width="100%" size="1"></td></tr>
		<tr><td colspan="2">Total: </td><td>' . $TCOUNTER . '</td>
		<td NOWRAP align="right">' . n_fmt($TOTAL1) . $CURR1 . '</td>
		  <td NOWRAP align="right">' . n_fmt($TOTAL2) . $CURR1 . '</td>
		  <td NOWRAP align="right">' . n_fmt($TOTAL10) . $CURR1 . '</td>
		  <td NOWRAP align="right">' . n_fmt($TOTAL3) . $CURR1 . '</td>
		  <td NOWRAP align="right">' . n_fmt($TOTAL4) . $CURR1 . '</td>
          <td NOWRAP align="right">' . n_fmt($TOTAL5) . $CURR1 . '</td>
          <td NOWRAP align="right">' . n_fmt($TOTAL6) . $CURR1 . '</td>
		  <td NOWRAP align="right">' . number_format($TOTAL7 / $TOTAL1, 4) . '</td>
          <td NOWRAP align="right">' . n_fmt($TOTAL7) . $CURR2 . '</td>
          <td NOWRAP align="right">' . number_format($TOTAL7 / $TOTAL8, 4) . '</td>
          <td NOWRAP align="right">' . n_fmt($TOTAL8) . $CURR1 . '</td>
          <td NOWRAP align="right">' . number_format(($TOTAL9 * 100) / $TOTAL1, 4) . '%</td>
          <td NOWRAP align="right">' . n_fmt($TOTAL9) . $CURR1 . '</td></tr>';
    $prtContent .= '</table>';

    echo $prtContent;
}
?>